﻿using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using Fuse8.DomainFramework.Common.Exceptions;
using Fuse8.DomainFramework.Common.Locator;
using Fuse8.DomainFramework.Domain;

namespace Fuse8.DomainFramework.DataAccess.SqlDatabase
{
	/// <summary>
	/// Schema builder implementation for ms sql
	/// </summary>
	public class MsSqlSchemaBuilder : ISqlSchemaBuilder
	{
        private static readonly ConcurrentDictionary<string, IList<SqlColumn>> _entityColumns = new ConcurrentDictionary<string, IList<SqlColumn>>();
        private static readonly ConcurrentDictionary<string, IList<SqlProcedureParameter>> _spParameters = new ConcurrentDictionary<string, IList<SqlProcedureParameter>>();
		private readonly object _syncRoot = new object();

		/// <summary>
		/// Get columns list for entity
		/// </summary>
		/// <param name="connection">Current connection</param>
		/// <typeparam name="T">Entity type</typeparam>
		/// <returns>List of columns for entity</returns>
		public virtual IList<SqlColumn> GetColumns<T>(SqlConnection connection)
		{
			return GetColumns(typeof (T), connection);
		}

		/// <summary>
		/// Get columns list for entity
		/// </summary>
		/// <param name="type">Entity type</param>
		/// <param name="connection">Current connection</param>
		/// <returns>List of columns for entity</returns>
		public virtual IList<SqlColumn> GetColumns(Type type, SqlConnection connection)
		{
			var key = string.Format("{0}{1}", connection.ConnectionString, type.Name);
		    return _entityColumns.GetOrAdd(key, index =>
		        {
                    var tableName = SqlEntityHelper.GetEntityTableName(type);
                    var tableColumns = GetTableColumns(type, tableName).ToList();
                    var columns = tableColumns.Where(p => SqlEntityHelper.IsColumnInEntity(type, p.SqlColumnName)).ToList();
                    columns.ForEach(p => p.EntityPropertyName = SqlEntityHelper.GetEntityColumnAlias(type, p.SqlColumnName));

                    var keyColumns = GetPrimaryKeys(type, tableName).ToList();

                    foreach (var keyColumn in keyColumns)
                    {
                        var column = columns.FirstOrDefault(p => p.SqlColumnName == keyColumn);
                        if (column == null)
                        {
                            column = new SqlColumn { SqlColumnName = keyColumn };
                            if (tableColumns.Any(c => c.SqlColumnName == keyColumn && c.IsIdentity))
                            {
                                column.IsIdentity = true;
                            }
                            columns.Add(column);
                        }
                        column.IsPrimaryKey = true;
                        column.EntityPropertyName = keyColumns.Count > 1 ? string.Format("Id_{0}", keyColumn) : keyColumn.ToLower() == "id" ? "" : "Id";
                    }

                    var ignoredColumns = SqlEntityHelper.GetIgnoredColumns(type).ToList();
                    return columns.Where(p => !ignoredColumns.Contains(p.SqlColumnName)).ToList();
		        });
		}

	    /// <summary>
	    /// 
	    /// </summary>
	    /// <param name="connection"></param>
	    /// <param name="transaction"> </param>
	    /// <param name="sp"></param>
	    /// <returns></returns>
        public virtual IList<SqlProcedureParameter> GetStoredProcedureParameters(SqlConnection connection, SqlTransaction transaction, string sp)
	    {
	        return _spParameters.GetOrAdd(sp, index =>
	                    {
	                        const string query =
	                                                      @"
select REPLACE(p.name, '@', '') Name
FROM sys.objects o
INNER JOIN sys.parameters p ON p.object_id = o.object_id
WHERE o.name = @p0

select REPLACE(p.name, '@', '') Name, c.name as column_name, c.system_type_id, c.max_length
from sys.parameters p
inner join sys.objects o on p.object_id = o.object_id
inner join sys.table_types tt on tt.user_type_id=p.user_type_id
inner join sys.columns c on c.object_id = tt.type_table_object_id
WHERE o.name = @p0
order by c.column_id";
	                        var command = new SqlCommand(query, connection, transaction);
	                        command.Parameters.Add("p0", SqlDbType.NVarChar).Value = sp;

	                        var list = new List<SqlProcedureParameter>();

	                        using (var reader = command.ExecuteReader())
	                        {
	                            while (reader.Read())
	                            {
	                                list.Add(new SqlProcedureParameter(reader["Name"].ToString()));
	                            }

	                            reader.NextResult();
	                            while (reader.Read())
	                            {
	                                var name = reader["Name"].ToString();
	                                var item = list.FirstOrDefault(p => p.Name == name);
	                                if (item != null)
	                                {
	                                    item.Columns.Add(new SqlColumn()
	                                                        {
	                                                            SqlColumnName = reader["column_name"].ToString()
	                                                        });
	                                }
	                            }

	                            reader.Close();
	                        }

	                        return list;

	                    });
		}

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
	    public virtual IList<Type> GetUpdateParts<T>()
	    {
	        return null;
	    }

	    private static IEnumerable<SqlColumn> GetTableColumns(Type type, string tableName)
		{		    
            string query =
		        @"select c.name, c.is_identity from sys.columns c
                             inner join sys.objects o on o.object_id = c.object_id
                             where o.name = @p0";
	        
            var command = SqlDatabaseHelper.CreateCommandForType(query, type);

		    command.Parameters.Add("p0", SqlDbType.NVarChar).Value = tableName;
            
            using(var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    yield return
                        new SqlColumn()
                            {
                                SqlColumnName = reader["Name"].ToString(),
                                IsIdentity = Convert.ToBoolean(reader["is_identity"])
                            };
                }

                reader.Close();
            }            
		}

		private static IEnumerable<string> GetPrimaryKeys(Type type, string tableName)
		{
		    var command = SqlDatabaseHelper.CreateCommandForType("sp_pkeys", type, CommandType.StoredProcedure);
			command.Parameters.Add("@table_name", SqlDbType.NVarChar).Value = tableName;
            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    yield return reader["COLUMN_NAME"].ToString();
                }
                reader.Close();
            }
		}
	}
}